

* +++++++++++++++++++++
* FIGURE A13: 
* VALIDATE
* KÖNIGSTEINER KEY
* +++++++++++++++++++++

* load admin data on refugees
import delimited "${data_raw}/Königsteiner_key/12531-0025.csv",  clear delimiter(";")

* give variables meaningful names
ren (v5 v6 v7 v8 v9 v10 v11 v12 v13 v14 v15 v16) ///
	(years_0 years_1_3 years_4_5 years_6_7 years_8_9 years_10_14 years_15_19 ///
	years_20_24 years_25_29 years_30_34 years_35_39 years_40_plus)

keep if v3 == "Insgesamt"

ren (v1 v2) (year bundesland)

replace year = substr(year,7,.)

foreach v of varlist years* {
	replace `v' = "0" if `v' =="-"
}
destring year years*, replace force

* collapse --> get rid of gender dimension
collapse (rawsum) years_*, by(year bundesland)

renvars years_*, prefix(refu_)
ren *years_* *yrs*

* create percentages of total 
foreach v of varlist *yrs* {
	qui: gen pct_of_`v' = .
	forval i = 2010 / 2019 {
		qui: su `v' if year ==`i', d
		qui: replace pct_of_`v' = (`v'/`r(sum)')*100 if year ==`i'
		}
	}

* reshape wide on year 
renvars *refu_*, suff(_)
reshape wide *refu_*, i(bundesland) j(year)		

* save	
tempfile num_refugees
save `num_refugees'

* load königsteiner key quotas
import excel using ///
	"${data_raw}/Königsteiner_key/Verteilungsquoten 2019.xlsx", ///
	clear firstrow

* merge with data on refugees
ren Bundesland bundesland
merge 1:1 bundesland using `num_refugees', assert(3) nogen

* binscatter: each year vs quota
reshape long pct_of_refu_yrs0_, i(bundesland) j(year)
keep if year >=2015
reg pct_of_refu_yrs0_ Quote
local slope : di %03.2f _b[Quote]
local se : di %03.2f _se[Quote]
corr pct_of_refu_yrs0_ Quote
local corr : di %03.2f `r(rho)'
binscatter pct_of_refu_yrs0_ Quote, ///
	ytitle("Percent of New Refugees" "Observed in Given Bundesland", size(large)) ///
	xtitle("Assignment Based on Königsteiner Schlüssel", size(large)) /// 
	note("Slope = `slope' (`se')" "Correlation = `corr'") ylabel(, labsize(large) nogrid) ///
	mcolor("black") lcolor("blue") xlabel(, labsize(large))
graph export "${output}/pct_of_refu_yrs0_tot_vs_ks.png", replace width(3000)	

